# 复制表结构 且复制数据
CREATE TABLE IF NOT EXISTS employees_copy
AS
(SELECT * FROM employees);

SELECT * FROM employees_blank;

DROP TABLE IF EXISTS employees_blank;

# 复制表结构 但是不复制数据
CREATE TABLE IF NOT EXISTS employees_blank
AS
(SELECT * FROM employees LIMIT 0);

CREATE TABLE IF NOT EXISTS employees_blank
AS
(SELECT * FROM employees WHERE FALSE);

DROP TABLE IF EXISTS employees_blank, employees_copy;

# 给表添加一个字段 默认添加为表中的最后一个字段
ALTER TABLE employees_copy
  ADD holiday DOUBLE(10, 2) DEFAULT 0 COMMENT '假期天数';
  
DESC employees_copy;

SHOW CREATE TABLE employees_copy;

# 给表添加一个字段 添加在第一列
ALTER TABLE employees_copy
  ADD test VARCHAR(15) FIRST;

# 给表添加一个字段 添加在某一列的后面
ALTER TABLE employees_copy
  ADD test2 VARCHAR(15) AFTER email;
  
/*
  给表添加字段
  ALTER TABLE ... ADD ...
    什么也不写 默认添加到最后一列
    FIRST 添加到第一列
    AFTER ... 添加到指定列的后面
*/

CREATE TABLE IF NOT EXISTS new_table (
  id INT,
  emp_name VARCHAR(15),
  email VARCHAR(15),
  telephone VARCHAR(15)
);

# 修改字段的类型、长度和默认值
# ALTER TABLE ... MODIFY
ALTER TABLE new_table
MODIFY emp_name CHAR(20) DEFAULT 'zhangsan';

# 修改字段的名字 这个不只是修改名字 是把整个字段全部改了
# ALTER TABLE ... CHANGE old_name new_name
ALTER TABLE new_table
CHANGE emp_name `name` VARCHAR(20);

ALTER TABLE new_table
CHANGE `name` `name` VARCHAR(20) DEFAULT '张三';

# 删除一个字段
ALTER TABLE new_table
DROP email;

# 重命名表
# RENAME TABLE old_name TO new_name
# ALTER TABLE old_name RENAME TO new_name
RENAME TABLE new_table TO old_table;

# 清空表
TRUNCATE TABLE employees_copy;

# 删除表
DROP TABLE IF EXISTS employees_copy, old_table;

INSERT INTO regions VALUES(5, 'China');
SELECT * FROM regions;
DELETE FROM regions WHERE region_id = 5;

# BEGIN;
INSERT INTO regions VALUES(5, 'China');
SELECT * FROM regions;
DELETE FROM regions WHERE region_id = 5;

SHOW VARIABLES LIKE 'auto%';

CREATE DATABASE exercise;
use exercise;
DROP DATABASE IF EXISTS exercise;

# 1
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8';
USE test01_office;

# 2
CREATE TABLE IF NOT EXISTS dept01 (
  id INT(7),
  NAME VARCHAR(25)
);

# 3
CREATE TABLE IF NOT EXISTS dept02
AS (
  SELECT * 
  FROM atguigudb.departments
);

SELECT * FROM dept02;

# 4
CREATE TABLE IF NOT EXISTS emp01 (
  id INT(7),
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  dept_id INT(7)
);

# 5
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);

DESC emp01;

# 6
CREATE TABLE IF NOT EXISTS emp02
AS
SELECT *
FROM atguigudb.employees;

SELECT * FROM emp02;

# 7
DROP TABLE IF EXISTS emp01;
SELECT * FROM emp01;

# 8
RENAME TABLE emp02 TO emp01;

# 9
ALTER TABLE dept02
ADD test_column VARCHAR(10);

DESC dept02;

ALTER TABLE emp01
ADD test_column VARCHAR(10);

DESC emp01;

# 10
ALTER TABLE emp01
DROP department_id;

DESC emp01;

# 1
CREATE DATABASE IF NOT EXISTS test02_market;

# 2
CREATE TABLE IF NOT EXISTS customer (
  c_num INT,
  c_name VARCHAR(50),
  c_contact VARCHAR(50),
  c_city VARCHAR(50),
  c_birth DATE
);

RENAME TABLE customer TO customers;

# 3 修改字段的位置既能用 MODIFY 也能用 CHANGE
ALTER TABLE customer
CHANGE c_contact c_contact VARCHAR(50) AFTER c_birth;

DESC customers;

ALTER TABLE customer
MODIFY c_contact VARCHAR(50) AFTER c_city;

# 4
ALTER TABLE customer
MODIFY c_name VARCHAR(70);

SHOW TABLES;
SELECT DATABASE();

# 5
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);

# 6
ALTER TABLE customers
ADD c_gender CHAR(1) AFTER c_name;

# 7
RENAME TABLES customers TO customers_info;
SHOW TABLES;

# 8
ALTER TABLE customers_info
DROP c_city;

# 1
CREATE DATABASE IF NOT EXISTS test03_company;

# 2
CREATE TABLE IF NOT EXISTS offices (
  officeCode INT,
  city VARCHAR(30),
  address VARCHAR(50),
  country VARCHAR(50),
  postalCode VARCHAR(25)
);

# 3
CREATE TABLE IF NOT EXISTS employees (
  empNum INT,
  lastName VARCHAR(50),
  firstName varchar(50),
  mobile varchar(25),
  `code` INT,
  jobTitle varchar(50),
  birth  DATE,
  note varchar(255),
  sex  varchar(5)
);

# 4
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER `code`;

# 5
ALTER TABLE employees
CHANGE birth birthday DATE;

# 6
ALTER TABLE employees
MODIFY sex CHAR(1);

# 7
ALTER TABLE employees
DROP note;

# 8
ALTER TABLE employees
ADD favoriate_activity VARCHAR(100);

# 9
RENAME TABLE employees TO employees_info;
SHOW TABLES;

#1. 创建数据库dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';

#2. 运行以下脚本创建表my_employees
USE dbtest11;
CREATE TABLE my_employees(
id INT(10),
first_name VARCHAR(10),
last_name VARCHAR(10),
userid VARCHAR(10),
salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);

SHOW TABLES;
DESC my_employees;

INSERT INTO my_employees VALUES
(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

DELETE FROM my_employees;
SELECT * FROM my_employees;

INSERT INTO users VALUES
(1, 'Rpatel', 10),
(2, 'Bdancs', 10),
(3, 'Bbiri', 20),
(4, 'Cnewman', 30),
(5, 'Aropebur', 40);

SELECT * FROM users;

UPDATE my_employees
SET last_name = 'drelxer'
WHERE id = 3;

UPDATE my_employees
SET salary = 1000
WHERE salary < 900;

DELETE u, e
FROM users u INNER JOIN my_employees e ON u.userid = e.userid
WHERE u.userid = 'Bbiri';

DELETE u, e
FROM users u, my_employees e;

SELECT * FROM my_employees;
SELECT * FROM users;

TRUNCATE TABLE my_employees;

 CREATE TABLE pet(
    `name` VARCHAR(20),
    `owner` VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth YEAR,
    death YEAR
 );
 
 INSERT INTO pet VALUES('Fluffy','harold','Cat','f','2013','2010');              
INSERT INTO pet(`name`,`owner`,species,sex,Birth) 
VALUES('Claws','gwen','Cat','m','2014'); 
INSERT INTO pet(`name`,species,sex,Birth) VALUES('Buffy','Dog','f','2009');
 INSERT INTO pet(`name`,`owner`,species,sex,Birth) 
VALUES('Fang','benny','Dog','m','2000');
 INSERT INTO pet VALUES('bowser','diane','Dog','m','2003','2009');
 INSERT INTO pet(`name`,species,sex,birth) VALUES('Chirpy','Bird','f','2008');
 
 ALTER TABLE pet
 ADD owner_birth DATE;
 
DESC pet;

SELECT * FROM pet;

UPDATE pet
SET `owner` = 'kevin'
WHERE `name` = 'Claws' AND species = 'Cat';

UPDATE pet
SET `owner` = 'duck'
WHERE death <=> NULL && species = 'Dog';

# 8
SELECT `name`, `owner`, death
FROM pet
WHERE death IS NOT NULL AND species = 'Cat';

# 9
DELETE FROM pet
WHERE species = 'Dog' && death IS NOT NULL;

# 10
SELECT * FROM pet;

CREATE TABLE employee(
 id INT,
 `name` VARCHAR(20),
 sex VARCHAR(2),
 tel VARCHAR(20),
 addr VARCHAR(50),
 salary DOUBLE
 );
 
INSERT INTO employee(id,`name`,sex,tel,addr,salary)VALUES
 (10001,'张一一','男','13456789000','山东青岛',1001.58),
 (10002,'刘小红','女','13454319000','河北保定',1201.21),
 (10003,'李四','男','0751-1234567','广东佛山',1004.11),
 (10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
 (10005,'王艳','男','020-1232133','广东广州',1405.16);
 
SELECT *
FROM employee
WHERE salary BETWEEN 1200 AND 1300;

SELECT id, `name`, addr
FROM employee
WHERE `name` REGEXP '^刘';

SELECT id, `name`, addr
FROM employee
WHERE `name` LIKE '刘%';

UPDATE employee
SET addr = '广东韶关'
WHERE `name` = '李四';

SELECT * FROM employee;

SELECT *
FROM employee
WHERE `name` REGEXP '小';

SELECT *
FROM employee
WHERE `name` LIKE '%小%';

# 你以为 CURRENT_TIMESTAMP 是时间戳吗？ 错了 是日期时间
SELECT CURRENT_TIMESTAMP;
SELECT LOCALTIMESTAMP;

# 这才是真时间戳
SELECT UNIX_TIMESTAMP();

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());